﻿using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration.Unity;
using Microsoft.Practices.Unity;
using Vallejos.Datos.Factory;
using Vallejos.Datos.Servicios;
using Vallejos.Entidades.Interfaces;
using Vallejos.Entidades.ModeloDeDatos;
using Vallejos.Excepciones;
using System.Linq;

namespace Vallejos.Datos.Accesos
{
    public class ComunDAL : IComun
    {
        private readonly DatabaseService _database;

        public ComunDAL()
        {
            var container = new UnityContainer()
                .AddNewExtension<EnterpriseLibraryCoreExtension>();
            _database = container.Resolve<DatabaseService>();
        }

        public InformacionDeEmpresa LeerInformacionDeEmpresa(int id)
        {
            InformacionDeEmpresa informacion = null;

            var factory = new InformacionDeEmpresaFactory();

            try
            {
                using (var command = _database.GetStoredProcCommand("LeerInformacionDeEmpresa"))
                {
                    _database.AddInParameter(command, "Id", DbType.Int32, id);

                    using (var reader = _database.ExecuteReader(command))
                        if (reader.Read()){
                            informacion = factory.Crear(reader);
                            informacion.DatosDomiciliarios =
                                this.LeerInformacionDomiciliaria(informacion.DatosDomiciliarios.Id);
                        }
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }

            return informacion;
        }

        public InformacionDomiciliaria LeerInformacionDomiciliaria(int id)
        {
            InformacionDomiciliaria informacion = null;

            var factory = new InformacionDomiciliariaFactory();

            try
            {
                using (var command = _database.GetStoredProcCommand("LeerInformacionDomiciliaria"))
                {
                    _database.AddInParameter(command, "Id", DbType.Int32, id);

                    using (var reader = _database.ExecuteReader(command))
                        if (reader.Read()){
                            informacion = factory.Crear(reader);;
                            informacion.Telefonos = LeerTelefonosDeDomicilio(informacion.Id);
                        }
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }

            return informacion;
        }

        public InformacionPersonal LeerInformacionPersonal(int id)
        {
            InformacionPersonal informacion = null;

            var factory = new InformacionPersonalFactory();

            try
            {
                using (var command = _database.GetStoredProcCommand("LeerInformacionPersonal"))
                {
                    _database.AddInParameter(command, "Id", DbType.Int32, id);

                    using (var reader = _database.ExecuteReader(command))
                        if (reader.Read())
                            informacion = factory.Crear(reader);
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }

            return informacion;
        }

        private List<Telefono> LeerTelefonosDeDomicilio(int idDomicilio)
        {
            var telefonos = new List<Telefono>();

            var factory = new TelefonoFactory();

            try
            {
                using (var command = _database.GetStoredProcCommand("LeerTelefonosDeDomicilio"))
                {
                    _database.AddInParameter(command, "Id_Domicilio", DbType.Int32, idDomicilio);

                    using (var reader = _database.ExecuteReader(command))
                        while (reader.Read())
                            telefonos.Add(factory.Crear(reader));
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }

            return telefonos;
        }

        private void  GuardarTelefonosDelDomicilio(InformacionDomiciliaria informacion)
        {
            try
            {
                foreach (var telefono in informacion.Telefonos.Where(tel => tel.Numero != string.Empty))
                {
                    using (var command = _database.GetStoredProcCommand("GuardarTelefono"))
                    {
                        if (telefono.Id != 0)
                            _database.AddInParameter(command, "Id", DbType.Int32, (int)telefono.Id);
                        else
                            _database.AddOutParameter(command, "Id", DbType.Int32, Int32.MaxValue);

                        _database.AddInParameter(command, "Numero", DbType.String, telefono.Numero);
                        _database.AddInParameter(command, "Id_Tipo", DbType.Int32, (int)telefono.Tipo.idTipo);
                        _database.AddInParameter(command, "Id_Domicilio", DbType.Int32, informacion.Id);

                        _database.ExecuteNonQuery(command);

                        if (telefono.Id == 0)
                            telefono.Id = Convert.ToInt32(_database.GetParameterValue(command, "Id"));
                    }
                }
                
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }
        }

        public List<Sexo> LeerSexos(int idLenguaje)
        {
            var sexos = new List<Sexo>();

            var factory = new SexoFactory();

            try
            {
                using (var command = _database.GetStoredProcCommand("LeerSexos"))
                {
                    _database.AddInParameter(command, "Id_Lenguaje", DbType.Int32, idLenguaje);

                    using (var reader = _database.ExecuteReader(command))
                        while (reader.Read())
                            sexos.Add(factory.Crear(reader));
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }

            return sexos;
        }


        public List<Provincia> LeerProvincias()
        {
            var provincias = new List<Provincia>();

            var factory = new ProvinciaFactory();

            try
            {
                using (var command = _database.GetStoredProcCommand("LeerProvincias"))
                {
                    using (var reader = _database.ExecuteReader(command))
                        while (reader.Read())
                            provincias.Add(factory.Crear(reader));
                    
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }

            return provincias;
        }

        public List<Localidad> LeerLocalidades()
        {
            var localidades = new List<Localidad>();

            var factory = new LocalidadFactory();

            try
            {
                using (var command = _database.GetStoredProcCommand("LeerLocalidades"))
                {
                    using (var reader = _database.ExecuteReader(command))
                        while (reader.Read())
                            localidades.Add(factory.Crear(reader));
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }

            return localidades;
        }

        internal InformacionPersonal GuardarInformacionPersonal(InformacionPersonal informacion)
        {
            try
            {
                using (var command = _database.GetStoredProcCommand("GuardarInformacionPersonal"))
                {
                    if (informacion.Id != 0)
                        _database.AddInParameter(command, "Id", DbType.Int32, informacion.Id);
                    else
                        _database.AddOutParameter(command, "Id", DbType.Int32, Int32.MaxValue);

                    _database.AddInParameter(command, "CUIL", DbType.String, informacion.CUIL);
                    _database.AddInParameter(command, "Nombre", DbType.String, informacion.Nombre);
                    _database.AddInParameter(command, "Apellido", DbType.String, informacion.Apellido);
                    _database.AddInParameter(command, "FechaDeNacimiento", DbType.DateTime, informacion.FechaDeNacimiento);
                    _database.AddInParameter(command, "Id_Sexo", DbType.Int32, (int)informacion.Sexo.IdSexo);

                    _database.ExecuteNonQuery(command);

                    if (informacion.Id == 0)
                        informacion.Id = Convert.ToInt32(_database.GetParameterValue(command, "Id"));
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }
            return informacion;
        }


        internal InformacionDomiciliaria GuardarInformacionDomiciliaria(InformacionDomiciliaria informacion)
        {
            try
            {
                using (var command = _database.GetStoredProcCommand("GuardarInformacionDomiciliaria"))
                {
                    if (informacion.Id != 0)
                        _database.AddInParameter(command, "Id", DbType.Int32, informacion.Id);
                    else
                        _database.AddOutParameter(command, "Id", DbType.Int32, Int32.MaxValue);

                    _database.AddInParameter(command, "Calle", DbType.String, informacion.Calle);
                    _database.AddInParameter(command, "Numero", DbType.Int32, informacion.Numero);
                    _database.AddInParameter(command, "Piso", DbType.Int32, informacion.Piso);
                    _database.AddInParameter(command, "Departamento", DbType.String, informacion.Departamento);
                    _database.AddInParameter(command, "CodigoPostal", DbType.String, informacion.CodigoPostal);
                    _database.AddInParameter(command, "Id_Provincia", DbType.Int32, informacion.Provincia.Id);
                    _database.AddInParameter(command, "Id_Localidad", DbType.Int32, informacion.Localidad.Id);

                    _database.ExecuteNonQuery(command);

                    if (informacion.Id == 0)
                        informacion.Id = Convert.ToInt32(_database.GetParameterValue(command, "Id"));
                }

                GuardarTelefonosDelDomicilio(informacion);
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }
            return informacion;
        }

        internal InformacionDeEmpresa GuardarInformacionDeEmpresa(InformacionDeEmpresa informacion)
        {
            try
            {
                informacion.DatosDomiciliarios = GuardarInformacionDomiciliaria(informacion.DatosDomiciliarios);

                using (var command = _database.GetStoredProcCommand("GuardarInformacionDeEmpresa"))
                {
                    if (informacion.Id != 0)
                        _database.AddInParameter(command, "Id", DbType.Int32, informacion.Id);
                    else
                        _database.AddOutParameter(command, "Id", DbType.Int32, Int32.MaxValue);

                    _database.AddInParameter(command, "Cuit", DbType.String, informacion.CUIT);
                    _database.AddInParameter(command, "Razonsocial", DbType.String, informacion.RazonSocial);
                    _database.AddInParameter(command, "Id_DatosDomiciliarios", DbType.String, informacion.DatosDomiciliarios.Id);

                    _database.ExecuteNonQuery(command);

                    if (informacion.Id == 0)
                        informacion.Id = Convert.ToInt32(_database.GetParameterValue(command, "Id"));
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }
            return informacion;
        }

        internal InformacionDeServicio GuardarInformacionDeServicio(InformacionDeServicio informacion)
        {
            try
            {
                using (var command = _database.GetStoredProcCommand("GuardarInformacionDeServicio"))
                {
                    _database.AddOutParameter(command, "Id", DbType.Int32, Int32.MaxValue);

                    _database.AddInParameter(command, "Id_Rubro", DbType.Int32, (int)informacion.Rubro.IdRubro);
                    _database.AddInParameter(command, "CUIT", DbType.String, informacion.CUIT);
                    _database.AddInParameter(command, "Id_EmpresaDeServicio", DbType.Int32, informacion.Empresa.Id);
                    _database.AddInParameter(command, "Monto", DbType.Decimal, informacion.Monto);
                    _database.AddInParameter(command, "Vencimiento", DbType.DateTime, informacion.Vencimiento);

                    _database.ExecuteNonQuery(command);

                    if (informacion.Id == 0)
                        informacion.Id = Convert.ToInt32(_database.GetParameterValue(command, "Id"));
                }
            }
            catch (Exception ex)
            {
                throw new ExcepcionDatos("Error desconocido", ex);
            }
            return informacion;
        }
    }
}